#!/var/bin/bash
# -*- coding: utf-8 -*-
import pymssql


class SQLServerManager(object):
    def __init__(self, host="localhost", user=None, password=None, database=None):
        self._host = host
        self._user = user
        self._password = password
        self._database = database
        self.db = None

    def connect(self):
        connect = pymssql.connect(server=self._host, user=self._user, password=self._password,
                                  database=self._database)
        if connect:
            print("连接成功!")
        return connect

    def create_table(self, data):
        connect = self.connect()
        cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
        cursor.execute("create table test(id varchar(20))")  # 执行sql语句
        connect.commit()  # 提交
        cursor.close()  # 关闭游标
        connect.close()  # 关闭连接

    def insert(self, table="v_Inspect", data=None):
        """
        数据插入
        :param table:
        :param data:
        :return:
        """
        sql = """INSERT INTO 
        %s
                (time, imgName,imgPath,imgStatus,imgShapes) 
        VALUES 
                ('%s','%s','%s','%s','%s' )""" % (table,
                                  data.get("time"), data.get("img_name"), data.get("img_path"),
                                  data.get("img_status"),
                                  data.get("img_shapes"))
        print(sql)
        # connect = self.connect()
        # cursor = connect.cursor()
        # cursor.execute(sql)
        # connect.commit()  # 提交
        # cursor.close()
        # connect.close()

    def select(self):
        connect = self.connect()
        cursor = connect.cursor()  # 创建一个游标对象,python里的sql语句都要通过cursor来执行
        sql = "select id, name from test"
        cursor.execute(sql)  # 执行sql语句
        row = cursor.fetchone()  # 读取查询结果,
        while row:  # 循环读取所有结果
            print("StationID=%s, name=%s" % (row[0], row[1]))  # 输出结果
            row = cursor.fetchone()
        cursor.close()
        connect.close()


if __name__ == '__main__':
    sql_server = SQLServerManager("SSS", "", "", "")
    data_info = {
        "time": "2022-06-16 13:55:30",
        "img_name": "specs#1_111#1_20220616135530.jpg",
        "img_path": "/opt/tsti/inspect/data/specs#1_111#1_20220616135530.jpg",
        "img_status": "OK",
        "img_shapes": ""
    }
    sql_server.insert("v_Inspect",data_info )
